#!/usr/bin/python
# -*- coding: UTF-8 -*-

################################################################################
#
# Copyright (c) 2020 openEuler.org, Inc. All Rights Reserved
#
################################################################################
"""
import data from excel

Authors: xiaojianghui
Date:    11/06/2020 11:01 AM
"""

import xlrd
import time
import os
import hashlib
from dbConnecttion.MysqlConn import Mysql
from downloadtask import downloadfiletask


def parse_excel(file_name):
    """
    parse excel, import data
    parse: file_name
    return: list
    """
    pack_name_list = []
    version_list = []
    branchs_list = []
    status_list = []
    total_list = []
    try:
        data = xlrd.open_workbook('./import_excels/' + file_name)
        table_one = data.sheet_by_name("Sheet1")
        row_number = table_one.nrows
        for i in range(1, row_number):
            pack_name_list.append(table_one.cell(i, 0).value)
        for i in range(1, row_number):
            version_list.append(table_one.cell(i, 1).value)
        for i in range(1, row_number):
            status_list.append(table_one.cell(i, 2).value)
        for i in range(1, row_number):
            branchs_list.append(table_one.cell(i, 3).value)
    except IndexError as e:
        print("Subscript out of bounds", e)
    except xlrd.XLRDError as e:
        print("Form not found：Sheet1", e)
    total_list.append(pack_name_list)
    total_list.append(version_list)
    total_list.append(status_list)
    total_list.append(branchs_list)
    return total_list


def cur_date():
    """
    current date
    :return createTime: string
    """
    create_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    return create_time


def import_data():
    """
        import excel
        :param
        :return none
    """
    downloadfiletask.handle_three()
    mysql = Mysql()
    files = os.listdir('./import_excels')
    for filename in files:
        with open('./import_excels/' + filename, 'rb') as f:
            sha1obj = hashlib.sha1()
            sha1obj.update(f.read())
            hash_value = sha1obj.hexdigest()
            print(filename, hash_value)
            f.close()
        sql = "select file_hash from cve_file_hash where file_name = %s"
        val = (filename,)
        file_hash = mysql.getOne(sql, val)
        if file_hash:
            if hash_value == file_hash['file_hash']:
                print("文件已解析:" + filename)
                os.remove('./import_excels/' + filename)
                continue
        sql = "insert into cve_file_hash (file_name, file_hash) values (%s, %s)"
        val = (filename, hash_value)
        mysql.insertOne(sql, val)
        mysql.dispose()
        result = parse_excel(filename)
        for i in range(0, len(result[0])):
            # sql = 'select * from cve_issue_repo_whitelist where package_name = %s and version = %s'
            # val = (result[0][i], result[1][i])
            sql = 'select * from cve_issue_repo_whitelist where package_name = %s'
            val = (result[0][i])
            mysql_data = mysql.getOne(sql, val)
            if mysql_data:
                print("更新数据package_name:" + result[0][i])
                sql = 'update cve_issue_repo_whitelist set status = %s, branchs = %s, update_time = %s where ' \
                      'package_name = %s and version = %s'
                val = (result[2][i], result[3][i], cur_date(), result[0][i], result[1][i])
                mysql.update(sql, val)
                mysql.dispose()
            else:
                print('插入数据')
                sql = 'insert into cve_issue_repo_whitelist (package_name, version, status, branchs, create_time, ' \
                      'update_time, delete_time) values (%s, %s, %s, %s, %s, %s, %s)'
                val = (result[0][i], result[1][i], result[2][i], result[3][i], cur_date(), None, None)
                mysql.insertOne(sql, val)
                mysql.dispose()
        os.remove('./import_excels/' + filename)
    mysql.close()
